﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SQLite;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data;
using System.Data.SQLite;
namespace mysix
{
    internal class dbmanager
    {
        private SQLiteConnection connection;
        string connectionString = "Data Source=mydatabase.db;Version=3;";
        private DataTable logsDataTable;
        public dbmanager()
        {
            // 创建 SQLite 连接
            
            
            CreateDatabaseAndTables();
        }

        public void CreateDatabaseAndTables()
        {
            try
            {
                // 打开连接
                connection = new SQLiteConnection(connectionString);
                connection.Open();

                // 创建 School 表
                string createSchoolTableSQL = "CREATE TABLE IF NOT EXISTS School (SchoolId INTEGER PRIMARY KEY, SchoolName TEXT)";
                SQLiteCommand createSchoolTableCmd = new SQLiteCommand(createSchoolTableSQL, connection);
                createSchoolTableCmd.ExecuteNonQuery();

                // 创建 Class 表
                string createClassTableSQL = "CREATE TABLE IF NOT EXISTS Class (SchoolId INTEGER, ClassId INTEGER PRIMARY KEY, ClassName TEXT)";
                SQLiteCommand createClassTableCmd = new SQLiteCommand(createClassTableSQL, connection);
                createClassTableCmd.ExecuteNonQuery();

                // 创建 Student 表
                string createStudentTableSQL = "CREATE TABLE IF NOT EXISTS Student (SchoolId INTEGER, ClassId INTEGER, StuId INTEGER PRIMARY KEY, StuName TEXT)";
                SQLiteCommand createStudentTableCmd = new SQLiteCommand(createStudentTableSQL, connection);
                createStudentTableCmd.ExecuteNonQuery();
                string createLogsTableSQL = "CREATE TABLE IF NOT EXISTS Logs (LogId INTEGER PRIMARY KEY, TableName TEXT, OperationType TEXT, Timestamp DATETIME, Description TEXT)";
                SQLiteCommand createLogsTableCmd = new SQLiteCommand(createLogsTableSQL, connection);
                createLogsTableCmd.ExecuteNonQuery();

                Console.WriteLine("SQLite database and tables created successfully.");
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                // 关闭连接
                connection.Close();
            }
        }
       public DataTable LoadLogsData()
        {
            DataTable logsDataTable = new DataTable(); // 创建一个 DataTable 来存储日志数据

            try
            {
                connection.Open();

                // 查询 Logs 表数据
                string selectLogsSQL = "SELECT LogId, TableName, OperationType, Timestamp, Description FROM Logs";
                SQLiteCommand selectLogsCmd = new SQLiteCommand(selectLogsSQL, connection);

                using (SQLiteDataAdapter adapter = new SQLiteDataAdapter(selectLogsCmd))
                {
                    adapter.Fill(logsDataTable);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                connection.Close();
            }

            return logsDataTable; // 返回包含日志数据的 DataTable
        }

        public void AddLog(string tableName, string operationType, string description)
        {
            try
            {
                // 打开连接
                connection.Open();

                // 插入日志记录
                string insertLogSQL = "INSERT INTO Logs (TableName, OperationType, Timestamp, Description) VALUES (@TableName, @OperationType, @Timestamp, @Description)";
                SQLiteCommand insertLogCmd = new SQLiteCommand(insertLogSQL, connection);
                insertLogCmd.Parameters.AddWithValue("@TableName", tableName);
                insertLogCmd.Parameters.AddWithValue("@OperationType", operationType);
                insertLogCmd.Parameters.AddWithValue("@Timestamp", DateTime.Now);
                insertLogCmd.Parameters.AddWithValue("@Description", description);
                insertLogCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
            finally
            {
                // 关闭连接
                connection.Close();
            }
        }
    }

   
}
